# -*- coding: utf-8 -*-
# @Time    : 2024/7/1 18:24
# @Author  : yujiahao
# @File    : 01_pooledb.py
# @description:数据库连接池


import pymysql
from dbutils.pooled_db import PooledDB
import logging

# 配置日志
logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')

# 创建连接池
pool = PooledDB(
    creator=pymysql,
    maxconnections=10,  # 连接池允许的最大连接数,如果数据库性能较弱，可以适当减少这些值，以防止过多的连接导致数据库过载。
    mincached=5,  # 初始化时创建的连接数
    maxcached=10,  # 连接池中最多闲置的连接数,同理
    blocking=True,  # 连接池中没有可用连接后是否阻塞等待
    maxusage=None,  # 单个连接的最大重复使用次数
    setsession=['SET AUTOCOMMIT = 1'],  # 会话参数
    ping=1,  # 检查连接是否可用
    host='127.0.0.1',
    port=33061,
    user='***',
    passwd='***',
    db='bigdata_test',
    charset='utf8'
)


def get_conn():
    return pool.connection()


# 简单查询
def simple_query(sql, params=None):
    conn = get_conn()
    try:
        with conn.cursor() as cursor:
            if params:
                cursor.execute(sql, params)
            else:
                cursor.execute(sql)
            result = cursor.fetchall()
            return result
    except pymysql.MySQLError as e:
        logging.error(f"错误：{e}")
    finally:
        conn.close()


def main():
    select_sql = """
    select * from bigdata_test.yujiahao;
    """

    # 执行查询
    results = simple_query(select_sql)
    for row in results:
        print(row)


if __name__ == '__main__':
    main()
